from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from mini_tools.uitool import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import time

class ShopeeRCFra2(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='旧拓展词功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ADTitKWFrame(self).pack(fill=X,padx=3,pady=3)

class ExpandKW(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.bmCount=IntVar()
        self.rbmCount=IntVar()
        self.searchStr=StringVar()
        self.searchStr.set('正在获取需采集长尾词搜索量...')
        self.createWidgets()
        Thread(target=self.get_search_count).start()
        #Thread(target=self.jccc).start()
    def createWidgets(self):


        f0=Frame(self)
        f0.pack(fill=X)
        f01=Frame(f0)
        f01.pack(fill=X)
        Label(f01, textvariable=self.searchStr,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)
        f00=Frame(f0)
        f00.pack(fill=X)
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        Label(f00, text='PID:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PIDIpt = Entry(f00,width=15)
        self.PIDIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_add_onekw=Button(f00,text=f'增加一词',command=lambda:Thread(target=self.add_onekw).start(),fg='#ffffff',bg='green')
        self.btn_add_onekw.pack(side=LEFT,padx=2, pady=2)

        self.btn_add_allkw=Button(f00,text=f'全部增加一词',command=lambda:Thread(target=self.add_allkw).start(),fg='#ffffff',bg='green')
        self.btn_add_allkw.pack(side=LEFT,padx=2, pady=2)

        self.btn_cj_search=Button(f00,text=f'采集搜索量',command=lambda:Thread(target=self.cj_search).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cj_search.pack(side=LEFT,padx=2, pady=2)

        self.btn_restart_task=Button(f00,text=f'重启任务',command=lambda:Thread(target=self.re_start).start(),fg='#ffffff',bg='green')
        self.btn_restart_task.pack(side=LEFT,padx=2, pady=2)
    
    def get_search_count(self):

        while True:
            try:
                rs0=self.ms.ExecQuery('select state from task where id=3')
                if rs0[0][0]==0:
                    self.btn_cj_search.config(state=NORMAL)
                    stt='未启动《搜索量采集》'
                else:
                    self.btn_cj_search.config(state=DISABLED)
                    stt='已启动《搜索量采集》'
                
                rs1=self.ms.ExecQuery('select count(*) from [ADTitKW] where searchcount is not null')
                rs2=self.ms.ExecQuery('select count(*) from [ADTitKW] where searchcount is null')
                self.searchStr.set(f'{stt},长尾词搜索量,已采集:{rs1[0][0]},未采集:{rs2[0][0]}')
     
            except:
                pass
            time.sleep(10)
    

    def add_onekw(self):
        
        pid=self.PIDIpt.get().strip()
        if not pid:
            messagebox.showinfo('提示','请先输入PID')
            return
        if not messagebox.askyesno('提示',f'是否增加PID:《{pid}》一词数量'):
            return

        self.btn_add_onekw.config(state=DISABLED)
        sqlstr='select Product_Name_Ad_Name,max(isYouHua) from YN_AD_Info where Product_ID=? group by Product_Name_Ad_Name'
        rs_p=self.ms.ExecQuery(sqlstr,(pid,))
        if len(rs_p)==0:
            messagebox.showinfo('提示',f'表《YN_AD_Info》找不到PID为:{pid}的数据')
            self.btn_add_onekw.config(state=NORMAL)
            return
        
        ystitle,isFg=rs_p[0]

        if isFg:
            messagebox.showinfo('提示',f'PID为:{pid}相关标题已经增加一词,无需再次添加')
            self.btn_add_onekw.config(state=NORMAL)
            return
        rcode=None
        ph_itemid=None
        cur_ad_name=ystitle
        mat=re.search(r'\|([\d\w]{8})\|$',cur_ad_name,re.M)
        if mat:
            rcode=mat.group(1)
            rs_ph=self.ms.ExecQuery('select itemid from RandomCodes where RandomCode=?',(rcode,))
            if rs_ph:
                ph_itemid=rs_ph[0][0]
            cur_ad_name=cur_ad_name.replace(f'|{rcode}|','')

        cur_ad_name=cur_ad_name.strip()

        rs_old_kw=self.ms.ExecQuery('select kw from ADTitKW where PTitle=?',(cur_ad_name,))
        old_kws=[r[0].lower()  for r in rs_old_kw]
        new_kws=[]
        kwList=[]
        for s in cur_ad_name.split():
            # 匹配只包含字母、数字和连字符，且不能是纯数字，并且字符串长度大于等于 2
            if re.fullmatch(r'[A-Za-z0-9\-]+', s) and not s.isdigit() and len(s) >= 2:
                if s.lower() not in kwList:
                    kwList.append(s.lower())
                if (s not in new_kws) and (s.lower() not in old_kws):
                    new_kws.append(s)
            
        print(f'PID:{pid},分割、过滤、去重后共获得:{len(new_kws)} 个一词,开始导入到《ADTitKW》表...')
        task_count=len(new_kws)
        for kw in new_kws:
            rs=self.ms.ExecQuery('select count(*) from ADTitKW where PTitle=? and kw=?',(cur_ad_name,kw))
            if rs[0][0]>0:
                print(f'《{kw}》,导入状态:已存在,剩余:{task_count}')
            else:
                aff=self.ms.ExecNoQuery(f'insert into ADTitKW(ProductID,kw,WordNum,PTitle,Rcode,PH_ItemID,KWList) values(?,?,?,?,?,?,?)',
                                        (pid,kw,1,cur_ad_name,rcode,ph_itemid,json.dumps(kwList)))
                print(f'《{kw}》,导入状态:{aff},剩余:{task_count}')
            task_count-=1
        
        aff2=ms.ExecNoQuery('update YN_AD_Info set isyouhua=1 where Product_Name_Ad_Name=?',(ystitle,))
        print(f'PID:{pid},上传一词完毕,标记YN_AD_Info标题:{aff2}行')
        messagebox.showinfo('提示',f'PID:{pid},上传一词完毕,标记YN_AD_Info标题:{aff2}行')

        self.btn_add_onekw.config(state=NORMAL)

    def add_allkw(self):
        
        
        self.btn_add_allkw.config(state=DISABLED)
        sqlstr='''select Product_Name_Ad_Name,min(Product_ID) pid,max(isYouHua) isfg from YN_AD_Info 
                where  updateTime> DATEDIFF(SECOND, '1970-01-01 00:00:00', DATEADD(HOUR, -8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)))
                group by Product_Name_Ad_Name
                '''
        rs_p=self.ms.ExecQuery(sqlstr)

        plist=[]
        for pname,pid,isfg in rs_p:
            if isfg==0:
                plist.append([pname,pid])

        if len(plist)==0:
            messagebox.showinfo('提示',f'表《YN_AD_Info》找不到的数据')
            self.btn_add_onekw.config(state=NORMAL)
            return
        if not messagebox.askyesno('提示',f'共有 {len(plist)} 个PID 需要增加一词,确定全部执行'):
            return
        aff2=0
        j=1
        for ystitle,pid in plist:
            print(f'开始第 {j}个,PID:{pid} 关键词增加一词...')
            rcode=None
            ph_itemid=None
            cur_ad_name=ystitle
            mat=re.search(r'\|([\d\w]{8})\|$',cur_ad_name,re.M)
            if mat:
                rcode=mat.group(1)
                rs_ph=self.ms.ExecQuery('select itemid from RandomCodes where RandomCode=?',(rcode,))
                if rs_ph:
                    ph_itemid=rs_ph[0][0]
                cur_ad_name=cur_ad_name.replace(f'|{rcode}|','')

            cur_ad_name=cur_ad_name.strip()

            rs_old_kw=self.ms.ExecQuery('select kw from ADTitKW where PTitle=?',(cur_ad_name,))
            old_kws=[r[0].lower()  for r in rs_old_kw]
            new_kws=[]
            kwList=[]
            for s in cur_ad_name.split():
                # 匹配只包含字母、数字和连字符，且不能是纯数字，并且字符串长度大于等于 2
                if re.fullmatch(r'[A-Za-z0-9_@%+\-]+', s) and not s.isdigit() and len(s) >= 2:
                    if s.lower() not in kwList:
                        kwList.append(s.lower())
                    if (s not in new_kws) and (s.lower() not in old_kws):
                        new_kws.append(s)
                
            print(f'PID:{pid},分割、过滤、去重后共获得:{len(new_kws)} 个一词,开始导入到《ADTitKW》表...')
            task_count=len(new_kws)
            for kw in new_kws:
                rs=self.ms.ExecQuery('select count(*) from ADTitKW where PTitle=? and kw=?',(cur_ad_name,kw))
                if rs[0][0]>0:
                    print(f'《{kw}》,导入状态:已存在,剩余:{task_count}')
                else:
                    aff=self.ms.ExecNoQuery(f'insert into ADTitKW(ProductID,kw,WordNum,PTitle,Rcode,PH_ItemID,KWList) values(?,?,?,?,?,?,?)',
                                            (pid,kw,1,cur_ad_name,rcode,ph_itemid,json.dumps(kwList)))
                    print(f'《{kw}》,导入状态:{aff},剩余:{task_count}')
                task_count-=1
            
            aff2+=ms.ExecNoQuery('update YN_AD_Info set isyouhua=1 where Product_Name_Ad_Name=?',(ystitle,))
            print(f'第{j} 个,PID:{pid},上传一词完毕,标记YN_AD_Info标题:{aff2}行,剩余:{len(plist)-j} 个')
            j+=1

        messagebox.showinfo('提示',f'上传一词完毕,成功上传{len(plist)} 个pid,共标记YN_AD_Info标题:{aff2}行')

        self.btn_add_allkw.config(state=NORMAL)
    
    def cj_search(self):
        self.btn_cj_search.config(state=DISABLED)

        if not messagebox.askyesno('提示','确定开启采集搜索量?'):
            return
        
        rs=self.ms.ExecQuery('select count(distinct [KW]) from [ADTitKW] where searchcount is not null')
        if rs[0][0]==0:
            print('当前表《ADTitKW》无广告词待采集')
        else:

            aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=3',(rs[0][0],))
            if aff>0:
                print(f'表《ADTitKW》成功启动采集搜索量任务,待采集数:{rs[0][0]}')
            else:
                print(f'表《ADTitKW》启动失败')

        
        self.btn_cj_search.config(state=NORMAL)

    def jccc(self):
        j=1
        while True:
            if j==1:
                time.sleep(600)
            self.re_start()
            time.sleep(600)
            print('等待10分钟下一次重启采集关键词...')
            j+=1

    def re_start(self):
        self.btn_restart_task.config(state=DISABLED)
        rs=self.ms.ExecQuery('select id from task where state=1 and (id between 7 and 12 or id=3)')
        aff3=ms.ExecNoQuery(f'update taskstatus set is_on=0 where (id between 7 and 12 or id=3) and is_on=1')
        if len(rs)>0:
            id_str= ','.join([str(r[0]) for r in rs])
            aff1=ms.ExecNoQuery(f'update task set state=-1 where id in ({id_str})')
           
            print(f'成功关闭{aff1}个采集关键词任务,等待60s,重启任务')
            time.sleep(60)
            aff2=ms.ExecNoQuery(f'update task set state=1 where id in ({id_str})')
            print(f'成功开启{aff2}个采集关键词任务')
        self.btn_restart_task.config(state=NORMAL)

class ADTitKWFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.strVars=StringVar(value='正在获取1~6词数量...')
        self.btnztStrs=StringVar(value='正在获取任务状态...')

        self.createWidgets()
        Thread(target=self.getcount).start()

    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='ADKWTit拓展词', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f2= Frame(self)
        f2.pack(fill=X)
        ExpandKW(f2).pack(fill=X)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)


        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)

        Label(f01, text=f'1~6词数量:', font=("微软雅黑", 12,"bold")).pack(side=LEFT,padx=2,pady=2)
        Label(f01, textvariable=self.strVars,fg='red',width=25,anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)


        f03=Frame(f1)
        f03.pack(fill=X,padx=2,pady=2)

        self.btn_cj=Button(f03,text='1~6词采集采集长尾词',state=DISABLED,command=lambda :Thread(target=self.start_task).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT,padx=2, pady=2)
        Label(f03, textvariable=self.btnztStrs,fg='red',width=20,anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)



    def getcount(self):

        while True:
            try:
                sqlstr1='''select 
                            count(case when state=1 then 1 end) as cjcount,
                            count(case when state=0 then 1 end) as nocjcount 
                            from ADTitKW where wordnum<=6
                            '''
                
                rs=self.ms.ExecQuery(sqlstr1)
                ycj=rs[0][0]
                wcj=rs[0][1]
                self.strVars.set(f'已采集:{ycj},未采集:{wcj}')
               
                
                sqlstr2='select id,state,tcount from task where id=7'
                rs2=ms.ExecQuery(sqlstr2)
                tid,state,tcont=rs2[0]
     
                if state==0:
                    self.btnztStrs.set(f'任务未开启')
                    self.btn_cj.config(state=NORMAL)
                else:
                    self.btnztStrs.set(f'任务已启动')
                    self.btn_cj.config(state=DISABLED)
            except:
                pass
            time.sleep(10)
    
    def start_task(self):

        if not messagebox.askyesno('提示',f'确定开启1~6词采集?'):
            return
        
        self.btn_cj.config(state=DISABLED)
        sqlstr1='select count(*) from ADTitKW where state=0 and wordnum<=6'
        rs1=self.ms.ExecQuery(sqlstr1)
        if rs1[0][0]==0:
            print(f'当前数据库没有1~6词待采集')
            messagebox.showinfo('提示',f'当前数据库没有1~6词待采集')
            self.btn_cj.config.config(state=NORMAL)
            return
        

        aff1=self.ms.ExecNoQuery('update [task] set state=1,tcount=? where [id]=7',(rs1[0][0],))
        if aff1:
            print(f'开启任务成功,开始采集 {rs1[0][0]} 个1~6词待采集')

        else:
            print('开启任务失败')
            messagebox.showerror('提示','开启任务失败')
            self.btn_cj.config(state=NORMAL)


# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("860x700")
# list_win.title('印尼虾皮群控操作')
# ShopeeRCFra2(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()